using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.Reflection;

namespace CEQuery
{
    public partial class FormQuery : Form
    {
        //private string ConnectionString = string.Empty;
        public string ConnectionString { get; set; }
        public List<CETable> CETables { get; private set; }
        private int tabCount = 1;
        public FormQuery()
        {
            InitializeComponent();
            CETables = new List<CETable>();
        }              
        private void FormQuery_Load(object sender, EventArgs e)
        {
            try
            {
                this.tvTables.ItemDrag += new ItemDragEventHandler(tvTables_ItemDrag);

                if (!string.IsNullOrEmpty(ConnectionString))
                {
                    string dbFile = ConnectionString.Split('=').Length > 1 ? ConnectionString.Split('=')[1] : string.Empty;
                    this.Text = string.IsNullOrEmpty(dbFile) ? string.Empty : dbFile.Substring(0, dbFile.LastIndexOf(';'));
                }
                AddQueryTab();
                BuildDBTree();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }        
        private void tvTables_ItemDrag(object sender, ItemDragEventArgs e)
        {
            DoDragDrop(e.Item, DragDropEffects.All);

        }
        private void tvTables_MouseUp(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                tvTables.SelectedNode = tvTables.GetNodeAt(e.X, e.Y);
                if (tvTables.SelectedNode != null && tvTables.SelectedNode.Level == 0)
                {
                    insertToolStripMenuItem.Enabled = false;
                    generateScriptToolStripMenuItem.Enabled = true;
                    selectToolStripMenuItem.Enabled = false;                    
                    deleteTableToolStripMenuItem.Enabled = false;
                    createTableToolStripMenuItem.Text = "Create Table";
                    manipulateKeysToolStripMenuItem.Enabled = false;
                }
                else if (tvTables.SelectedNode != null && tvTables.SelectedNode.Nodes.Count > 0)
                {
                    //ctxDataMenu.Visible = true;
                    insertToolStripMenuItem.Enabled = true;
                    generateScriptToolStripMenuItem.Enabled = true;
                    selectToolStripMenuItem.Enabled = true;
                    deleteTableToolStripMenuItem.Enabled = true;
                    createTableToolStripMenuItem.Text = "Edit Table";
                    manipulateKeysToolStripMenuItem.Enabled = true;
                }
                else
                {
                    //ctxDataMenu.Visible = false;
                    insertToolStripMenuItem.Enabled = false;
                    generateScriptToolStripMenuItem.Enabled = false;
                    selectToolStripMenuItem.Enabled = false;
                    deleteTableToolStripMenuItem.Enabled = false;
                    createTableToolStripMenuItem.Text = "Create Table";
                    manipulateKeysToolStripMenuItem.Enabled = false;
                }
            }
        }
        private void selectToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //RunSelectTable(tvTables.SelectedNode.Text);
            AddDataManipulationTab(tvTables.SelectedNode.Text);
        }
        private void insertToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataManipulation dm = new DataManipulation();
            dm.TableName = tvTables.SelectedNode.Text;
            dm.Operation = DataOperation.INSERT;
            dm.ConnectionString = ConnectionString;
            if (dm.ShowDialog() == DialogResult.OK)
            {
                AddInsertQueryTab(dm.Query);
            }
        }        
        private void FormQuery_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F6)
            {
                //this.tabQuery.TabPages.Add(new QueryTab());
                AddQueryTab();
            }
        }
        public void AddQueryTab()
        {
            string tabCaption = "Query " + tabCount.ToString();
            tabCount++;
            QueryPaneControl ctrl = new QueryPaneControl();
            ctrl.StatusEvent += new StatusEventHandler(ctrl_StatusEvent);
            ctrl.RefreshRequestEvent += new RefreshRequestEventHandler(ctrl_RefreshRequestEvent);
            ctrl.ConnectionString = this.ConnectionString;
            ctrl.Dock = DockStyle.Fill;
            TabPages.TabPage page = new TabPages.TabPage(tabCaption,ctrl,string.Empty);
            tabQuery.Add(page);
            tabQuery.CurrentPage = page;            
        }
        void ctrl_RefreshRequestEvent(object sender, EventArgs e)
        {
            BuildDBTree();
        }        
        public void AddInsertQueryTab(string insertQuery)
        {
            string tabCaption = "Query " + tabCount.ToString();
            tabCount++;
            QueryPaneControl ctrl = new QueryPaneControl();
            ctrl.StatusEvent += new StatusEventHandler(ctrl_StatusEvent);
            ctrl.RefreshRequestEvent += new RefreshRequestEventHandler(ctrl_RefreshRequestEvent);
            ctrl.ConnectionString = this.ConnectionString;
            ctrl.TableInsertQuery = insertQuery;
            ctrl.Dock = DockStyle.Fill;
            TabPages.TabPage page = new TabPages.TabPage(tabCaption, ctrl, string.Empty);
            tabQuery.Add(page);
            tabQuery.CurrentPage = page;            
        }
        private void AddDataManipulationTab(string tableName)
        {
            string tabCaption = "Query " + tabCount.ToString();
            tabCount++;
            QueryPaneControl ctrl = new QueryPaneControl();
            ctrl.StatusEvent += new StatusEventHandler(ctrl_StatusEvent);
            ctrl.RefreshRequestEvent += new RefreshRequestEventHandler(ctrl_RefreshRequestEvent);
            ctrl.ConnectionString = this.ConnectionString;
            ctrl.TableSelectQuery = "SELECT * FROM [" + tableName + "]";
            ctrl.UpdatablePane = true;
            ctrl.Dock = DockStyle.Fill;
            TabPages.TabPage page = new TabPages.TabPage(tabCaption, ctrl, string.Empty);
            tabQuery.Add(page);
            tabQuery.CurrentPage = page;            
        }
        public void RunActiveQuery()
        {
            if (tabQuery.Count > 0)
            {
                ((QueryPaneControl)tabQuery.CurrentPage.Control).RunQuery();
            }
        }
        public bool SaveActiveQuery()
        {
            if (tabQuery.Count > 0)
            {
                return ((QueryPaneControl)tabQuery.CurrentPage.Control).SaveQuery();
            }
            return false;
        }
        private void BuildDBTree()
        {
            tvTables.Nodes.Clear();
            if (!string.IsNullOrEmpty(ConnectionString))
            {
                using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                {

                    SqlCeCommand cmd = new SqlCeCommand();
                    cmd.Connection = connection;
                    connection.Open();
                    cmd.CommandText = "SELECT table_name FROM information_schema.tables";
                    SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
                    DataSet dsTable = new DataSet();
                    da.Fill(dsTable);

                    cmd.CommandText = "SELECT Table_name, Column_name, Data_type,Character_maximum_length FROM information_schema.columns";
                    da = new SqlCeDataAdapter(cmd);
                    DataSet dsColumns = new DataSet();
                    da.Fill(dsColumns);

                    cmd.CommandText = "SELECT u.TABLE_NAME,u.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS u ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME WHERE c.CONSTRAINT_TYPE = 'PRIMARY KEY'";
                    da = new SqlCeDataAdapter(cmd);
                    DataSet dsPKColumns = new DataSet();
                    da.Fill(dsPKColumns);

                    tvTables.Nodes.Clear();
                    TreeNode rootNode = new TreeNode(string.IsNullOrEmpty(this.Text) ? "[Unknown]" : this.Text.Substring(this.Text.LastIndexOf("\\") + 1));
                    rootNode.ImageIndex = 3;
                    rootNode.SelectedImageIndex = 3;
                    tvTables.Nodes.Add(rootNode);
                    TreeNode node, childNode;

                    CETable tbl;
                    foreach (DataRow dr in dsTable.Tables[0].Rows)
                    {
                        node = new TreeNode(dr["table_name"].ToString());
                        tbl = new CETable();
                        tbl.TableName = dr["table_name"].ToString();
                        
                        node.Tag = dr["table_name"].ToString();
                        node.ImageIndex = 0;
                        node.SelectedImageIndex = 0;
                        DataRow[] columns = dsColumns.Tables[0].Select("Table_name = '" + dr["table_name"].ToString() + "'");
                        foreach (DataRow col in columns)
                        {                            
                            childNode = new TreeNode(col["Column_name"].ToString() + " (" + col["Data_type"].ToString() + (col["Character_maximum_length"] != DBNull.Value && !(col["Data_type"].ToString() == "ntext" || col["Data_type"].ToString() == "image") ? string.Concat("[", col["Character_maximum_length"].ToString(), "]") : string.Empty) + ")");
                            tbl.Columns.Add(col["Column_name"].ToString());
                            childNode.Tag = col["Column_name"].ToString();
                            DataRow[] pks = dsPKColumns.Tables[0].Select("Table_name = '" + dr["table_name"].ToString() + "' AND COLUMN_NAME = '" + col["Column_name"].ToString() + "'");

                            if (pks.Length > 0)
                            {
                                childNode.ImageIndex = 1;
                                childNode.SelectedImageIndex = 1;
                            }
                            else
                            {
                                childNode.ImageIndex = 2;
                                childNode.SelectedImageIndex = 2;
                            }
                            node.Nodes.Add(childNode);
                        }
                        //tvTables.Nodes.Add(node);
                        rootNode.Nodes.Add(node);
                        CETables.Add(tbl);
                    }
                    rootNode.Expand();
                }

            }
            else
            {
                MessageBox.Show("Select a DB first");
            }
        }
        private void generateScriptToolStripMenuItem_Click(object sender, EventArgs e)
        {
            ScriptGenerator frmScript = new ScriptGenerator();
            frmScript.TableName = tvTables.SelectedNode.Level == 0 ? "ALL TABLE" : tvTables.SelectedNode.Text;
            frmScript.ConnectionString = ConnectionString;
            if (frmScript.ShowDialog() == DialogResult.OK)
            {
                ((MainForm)this.MdiParent).toolStripStatusLabel.Text = "Script has been generated successfully.";
            }
        }
        void ctrl_StatusEvent(object sender, StatusEventArgs e)
        {
           ((MainForm)this.MdiParent).toolStripStatusLabel.Text = string.IsNullOrEmpty(e.Message) ? string.Empty : e.Message;
        }
        private void refreshTreeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            BuildDBTree();
        }
        private void createTableToolStripMenuItem_Click(object sender, EventArgs e)
        {
            CreateTable ct = new CreateTable();
            if (tvTables.SelectedNode != null && tvTables.SelectedNode.Nodes.Count > 0)
            {
                ct.SelectedTable = tvTables.SelectedNode.Text;
                ct.ConnectionString = this.ConnectionString;
            }
            if (ct.ShowDialog() == DialogResult.OK)
            {
                AddInsertQueryTab(ct.TableScript);
            }
        }
        private void deleteTableToolStripMenuItem_Click(object sender, EventArgs e)
        {
            AddInsertQueryTab(string.Format("DROP TABLE {0}", tvTables.SelectedNode.Text));
        }

        private void manipulateKeysToolStripMenuItem_Click(object sender, EventArgs e)
        {
            KeyManipulation keyManipulation = new KeyManipulation();            
            keyManipulation.TableName = tvTables.SelectedNode.Level == 0 ? string.Empty : tvTables.SelectedNode.Text;
            keyManipulation.ConnectionString = ConnectionString;
            keyManipulation.CETables = this.CETables;
            if (keyManipulation.ShowDialog() == DialogResult.OK)
            {
                AddInsertQueryTab(keyManipulation.ConstraintScript);
            }
        }
    }
}